package com.example.bestudentsystem.demos.web.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.example.bestudentsystem.demos.web.pojo.Course;
import com.example.bestudentsystem.demos.web.vo.CourseVo;
import com.example.bestudentsystem.demos.web.vo.HistoryVo;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;

@Mapper
public interface CourseMapper extends BaseMapper<Course> {
    @Select("select course.cno,course.cname,course.credit,course.time,course.address,teacher.tname from course JOIN teacher ON course.tno=teacher.tno \n" +
            " JOIN sc ON sc.cno=course.cno WHERE sc.sno=#{sno}")
    List<CourseVo> getCourseBySno(String sno);

    @Select("select * from course WHERE tno=#{tno}")
    List<Course> getCourseByTno(String tno);

    @Select("select course.cno,course.cname,course.time,course.address,teacher.tname,history.htime,history.type from course JOIN teacher ON course.tno=teacher.tno \n" +
            " JOIN history ON history.cno=course.cno WHERE history.sno=#{sno} ")
    List<HistoryVo> getCourseHistory(String sno);

    @Select("SELECT course.cno,course.cname,course.time,course.address,teacher.tname,course.residue FROM course \n" +
            "JOIN teacher ON course.tno = teacher.tno LEFT JOIN sc ON course.cno = sc.cno AND sc.sno = #{sno}\n" +
            "WHERE sc.sno IS NULL AND course.isopen = 1  ")
    List<CourseVo> getSecourse(String sno);
    @Select("SELECT course.cno,course.cname,course.time,course.address,teacher.tname,course.photo,course.isopen FROM course \n" +
            "JOIN teacher ON course.tno = teacher.tno JOIN sc ON course.cno = sc.cno\n" +
            "WHERE sc.sno =#{sno}   ")
    List<CourseVo> getMycourse(String sno);
    @Delete("DELETE FROM sc WHERE sno=#{sno} AND cno=#{cno}")
    void delMycourse(String sno,String cno);

    @Update("UPDATE course SET residue=residue + #{num} WHERE cno=#{cno}")
    void UpdateResidue(String cno,int num);
    //更改课程状态（0为未开放，1为已开放）
    @Update("update course set israte=#{israte} where cno=#{cno}")
    void updateByCno(int israte,String cno);
    @Select("SELECT course.time FROM course " +
            "JOIN sc ON course.cno = sc.cno " +
            "WHERE sc.sno = #{sno}")
    List<String> getStudentCourseTimes(String sno);
    //分页查找未开放的课程
    @Select("select * from course where israte=0")
    IPage<Course> findCloseByPage(IPage<Course> iPage);
    //分页查找已开放的课程
    @Select("select * from course where israte=1")
    IPage<Course> findOpenByPage(IPage<Course> iPage);
}
